package Com.Ring.TradingMobile.SQLite;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import Com.Ring.TradingMobile.Object.CompanyInfo;
import Com.Ring.TradingMobile.Object.Config;
import Com.Ring.TradingMobile.Object.DanhMuc;
import Com.Ring.TradingMobile.Object.ItemDanhMuc;
import Com.Ring.TradingMobile.Object.MarketInfo;
import Com.Ring.TradingMobile.Object.StockInfo;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBManager extends SQLiteOpenHelper {

	private static final String DatabaseName = "RTStockData.db";
	private static final int Version = 2;
	private static String TAG = "DBManagerDebug";
	private static String DATABASE_CREATE_TABLE = "";
	private SQLiteDatabase DBSelect = null;

	public DBManager(Context context) {
		super(context, DatabaseName, null, Version);
	}

	public Cursor Select(String sql) {
		Cursor cur = null;
		if (DBSelect != null && DBSelect.isOpen())
			DBSelect.close();
		try {
			DBSelect = this.getReadableDatabase();
			return DBSelect.rawQuery(sql, null);
		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		}
		Log.d(TAG, "Khong Phai Return Cuoi Dong");
		return cur;
	}

	public void UpdateDanhMuc(String sql) {
		SQLiteDatabase DB = null;
		try {
			DB = this.getWritableDatabase();
			DB.execSQL(sql, null);
		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		} finally {
			if (DB != null) {
				DB.close();
			}
		}
	}

	public void InsertDanhMuc(String tenDanhMuc) {
		SQLiteDatabase DB = null;
		try {
			DB = this.getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(DanhMuc.NAME_DANH_MUC, tenDanhMuc);
			String whereClause = DanhMuc.NAME_DANH_MUC + " = ? ";

			int rows = DB.update(DanhMuc.TABLE_NAME, values, whereClause,
					new String[]{tenDanhMuc});
			if (rows <= 0) {
				DB.insertOrThrow(DanhMuc.TABLE_NAME, null, values);
			}

		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		} finally {
			if (DB != null) {
				DB.close();
			}
		}
	}
	public void DeleteDanhMuc(Object... obj) {

		SQLiteDatabase DB = null;
		try {
			DB = this.getWritableDatabase();

			for (int i = 0; i < obj.length; i++) {
				// xoa item danh muc truoc khi xoa danh muc
				String sqlDeleteItemDanhMuc = "delete from "
						+ ItemDanhMuc.TABLE_NAME + " where "
						+ ItemDanhMuc.ID_DANH_MUC + " = (select "
						+ DanhMuc.ID_DANH_MUC + " from " + DanhMuc.TABLE_NAME
						+ " where " + DanhMuc.NAME_DANH_MUC + " = '"
						+ obj[i].toString() + "')";
				DB.execSQL(sqlDeleteItemDanhMuc);
				//
				String where = DanhMuc.NAME_DANH_MUC + " = '"
						+ obj[i].toString() + "'";
				DB.delete(DanhMuc.TABLE_NAME, where, null);
			}
		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		} finally {
			if (DB != null) {
				DB.close();
			}
		}

	}

	public void InsertCompanyList(List<CompanyInfo> companyList) {
		SQLiteDatabase DB = null;
		try {
			DB = this.getWritableDatabase();
			for (int i = 0; i < companyList.size(); i++) {
				CompanyInfo company = companyList.get(i);
				String sql = "INSERT INTO " + CompanyInfo.TABLE_NAME + " ("
						+ CompanyInfo.MARKETID + "," + CompanyInfo.CODE + ","
						+ CompanyInfo.FULLNAME + ") SELECT " + company.MarketId
						+ ",'" + company.Code + "','" + company.FullName
						+ "' WHERE NOT EXISTS (SELECT 1 FROM "
						+ CompanyInfo.TABLE_NAME + " WHERE " + CompanyInfo.CODE
						+ " = '" + company.Code + "');";
				DB.execSQL(sql);
			}
		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		} finally {
			if (DB != null) {
				DB.close();
			}
		}
	}

	public void InsertStockInfoList(List<StockInfo> listStockInfo) {
		SQLiteDatabase DB = null;
		try {
			DB = this.getWritableDatabase();
			for (int i = 0; i < listStockInfo.size(); i++) {
				StockInfo stockinfo = listStockInfo.get(i);
				ContentValues values = new ContentValues();

				values.put(StockInfo.SYMBOL, stockinfo.Symbol);
				values.put(StockInfo.REF_PRICE, stockinfo.RefPrice);
				values.put(StockInfo.CEIL_PRICE, stockinfo.CeilPrice);
				values.put(StockInfo.FLOOR_PRICE, stockinfo.FloorPrice);
				values.put(StockInfo.PRICE_MATCH, stockinfo.PriceMatch);
				values.put(StockInfo.CHANGED, stockinfo.Changed);
				values.put(StockInfo.PER_CHANGED, stockinfo.PerChanged);
				values.put(StockInfo.VOLUME_MATCH, stockinfo.VolumeMatch);
				values.put(StockInfo.TOTAL_MATCH, stockinfo.TotalMatch);
				values.put(StockInfo.BEST_PRICE_BUY, stockinfo.BestPriceBuy);
				values.put(StockInfo.BEST_VOL_BUY, stockinfo.BestVolbuy);
				values.put(StockInfo.BEST_PRICE_SELL, stockinfo.BestPriceSell);
				values.put(StockInfo.BEST_VOL_SELL, stockinfo.BestVolSell);
				values.put(StockInfo.TRADE_DATE, stockinfo.TradeDate);
				String whereClause = StockInfo.SYMBOL + " = ? and "
						+ StockInfo.TRADE_DATE + " = ? ";
				int rows = DB.update(StockInfo.TABLE_NAME, values, whereClause,
						new String[]{stockinfo.Symbol, stockinfo.TradeDate});
				if (rows <= 0) {
					DB.insertOrThrow(StockInfo.TABLE_NAME, null, values);
				}
			}

		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		} finally {
			if (DB != null) {
				DB.close();
			}
		}
	}

	public void DeleteItemDanhMuc(int idDanhMuc, String code) {
		SQLiteDatabase DB = null;
		try {
			DB = this.getWritableDatabase();
			ContentValues values = new ContentValues();

			values.put(ItemDanhMuc.CODE, code);
			values.put(ItemDanhMuc.ID_DANH_MUC, idDanhMuc);

			String whereClause = ItemDanhMuc.ID_DANH_MUC + " = ? and "
					+ ItemDanhMuc.CODE + " = ? ";
			DB.delete(
					ItemDanhMuc.TABLE_NAME,
					whereClause,
					new String[]{String.valueOf(idDanhMuc),
							String.valueOf(code)});

		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		} finally {
			if (DB != null) {
				DB.close();
			}
		}
	}

	public void InsertItemDanhMuc(int idDanhMuc, String code) {
		SQLiteDatabase DB = null;
		try {
			DB = this.getWritableDatabase();
			ContentValues values = new ContentValues();

			values.put(ItemDanhMuc.CODE, code);
			values.put(ItemDanhMuc.ID_DANH_MUC, idDanhMuc);

			String whereClause = ItemDanhMuc.ID_DANH_MUC + " = ? and "
					+ ItemDanhMuc.CODE + " = ? ";
			int rows = DB.update(
					ItemDanhMuc.TABLE_NAME,
					values,
					whereClause,
					new String[]{String.valueOf(idDanhMuc),
							String.valueOf(code)});
			if (rows <= 0) {
				DB.insertOrThrow(ItemDanhMuc.TABLE_NAME, null, values);
			}

		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		} finally {
			if (DB != null) {
				DB.close();
			}
		}
	}

	public void InsertItemDanhMuc(ItemDanhMuc item) {
		SQLiteDatabase DB = null;
		try {
			DB = this.getWritableDatabase();
			ContentValues values = new ContentValues();

			values.put(ItemDanhMuc.CODE, item.Code);
			values.put(ItemDanhMuc.ID_DANH_MUC, item.Id_DanhMuc);

			String whereClause = ItemDanhMuc.ID_DANH_MUC + " = ? and "
					+ ItemDanhMuc.CODE + " = ? ";
			int rows = DB.update(
					ItemDanhMuc.TABLE_NAME,
					values,
					whereClause,
					new String[]{String.valueOf(item.Id_DanhMuc),
							String.valueOf(item.Code)});
			if (rows <= 0) {
				DB.insertOrThrow(ItemDanhMuc.TABLE_NAME, null, values);
			}

		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		} finally {
			if (DB != null) {
				DB.close();
			}
		}
	}

	public void InsertMarketInfo(MarketInfo marketInfo) {
		SQLiteDatabase DB = null;
		try {
			DB = this.getWritableDatabase();
			ContentValues values = new ContentValues();

			values.put(MarketInfo.MARKET_ID, marketInfo.marketid);
			//values.put(MarketInfo.COUNT_FLOOR, marketInfo.CountFloor);
			values.put(MarketInfo.INDEX_CHANGED, marketInfo.IndexChanged);
			values.put(MarketInfo.SET_INDEX, marketInfo.SetIndex);
			values.put(MarketInfo.STATUS, marketInfo.Status);
			values.put(MarketInfo.TIME, marketInfo.Time);
			values.put(MarketInfo.TOTAL_SHARES, marketInfo.TotalShares);
			values.put(MarketInfo.TOTAL_VALUES, marketInfo.TotalValues);
			values.put(MarketInfo.TRADE_DATE, marketInfo.TradeDate);

			String whereClause = MarketInfo.TRADE_DATE + " = ? and "
					+ MarketInfo.TIME + " = ? ";
			int rows = DB.update(
					MarketInfo.TABLE_NAME,
					values,
					whereClause,
					new String[]{marketInfo.TradeDate,
							String.valueOf(marketInfo.Time)});
			if (rows <= 0) {
				DB.insertOrThrow(MarketInfo.TABLE_NAME, null, values);
			}

		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		} finally {
			if (DB != null) {
				DB.close();
			}
		}
	}

	public void SaveConfig(int interval, int isRealTime, int isAutoSave,
			int DaysClearData) {
		SQLiteDatabase DB = null;
		try {
			DB = this.getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(Config.AUTO_CLEAR_TIME, DaysClearData);
			values.put(Config.AUTO_SAVE, isAutoSave);
			values.put(Config.INTERVAL, interval);
			values.put(Config.IS_REALTIME, isRealTime);
			int rows = DB.update(Config.TABLE_NAME, values, null, null);
			if (rows <= 0) {
				DB.insertOrThrow(Config.TABLE_NAME, null, values);
			}
		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		} finally {
			if (DB != null) {
				DB.close();
			}
		}
	}

	public void InitDataConfig() {
		SQLiteDatabase DB = null;
		try {
			DB = this.getWritableDatabase();
			ContentValues values = new ContentValues();

			Cursor cur = Select("select * from " + Config.TABLE_NAME);
			if (cur.getCount() <= 0) {
				values.put(Config.AUTO_CLEAR_TIME, 30);
				values.put(Config.AUTO_SAVE, 1);
				values.put(Config.INTERVAL, 30000);
				values.put(Config.IS_REALTIME, 1);
				DB.insertOrThrow(Config.TABLE_NAME, null, values);
			}else {
				cur.moveToNext();
				int DaysClear = cur.getInt(cur.getColumnIndex(Config.AUTO_CLEAR_TIME));
				final Calendar c = Calendar.getInstance();
				c.add(Calendar.DATE, -DaysClear);
				DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
				String strDate = dateFormat.format(c.getTime());
				String whereArg = StockInfo.TRADE_DATE+"<'?'";
				DB.delete(StockInfo.TABLE_NAME, whereArg, new String[] {strDate});
				DB.delete(MarketInfo.TABLE_NAME, whereArg, new String[] {strDate});
			}
		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		} finally {
			if (DB != null) {
				DB.close();
			}
		}
	}

	@Override
	public void onCreate(SQLiteDatabase DB) {
		try {
			// create table DanhMuc
			DATABASE_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS "
					+ DanhMuc.TABLE_NAME
					+ " (_id integer primary key autoincrement,"
					+ DanhMuc.NAME_DANH_MUC + " text not null);";
			DB.execSQL(DATABASE_CREATE_TABLE);
			// create table ItemDanhMuc
			DATABASE_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS "
					+ ItemDanhMuc.TABLE_NAME
					+ " (_id integer primary key autoincrement, "
					+ ItemDanhMuc.CODE + " text not null, "
					+ ItemDanhMuc.ID_DANH_MUC + " integer);";
			DB.execSQL(DATABASE_CREATE_TABLE);
			// Create table CompnayInfo
			DATABASE_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS "
					+ CompanyInfo.TABLE_NAME
					+ " (_id integer primary key autoincrement, "
					+ CompanyInfo.MARKETID + " integer, " + CompanyInfo.CODE
					+ " text not null, " + CompanyInfo.FULLNAME + " text);";
			DB.execSQL(DATABASE_CREATE_TABLE);
			// Create table StockInfo
			DATABASE_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS "
					+ StockInfo.TABLE_NAME
					+ " (_id integer primary key autoincrement, "
					+ StockInfo.SYMBOL + " text, " + StockInfo.REF_PRICE
					+ " REAL, " + StockInfo.CEIL_PRICE + " REAL, "
					+ StockInfo.FLOOR_PRICE + " REAL, " + StockInfo.PRICE_MATCH
					+ " REAL, " + StockInfo.CHANGED + " REAL, "
					+ StockInfo.PER_CHANGED + " REAL, "
					+ StockInfo.VOLUME_MATCH + " REAL, "
					+ StockInfo.TOTAL_MATCH + " REAL, "
					+ StockInfo.BEST_PRICE_BUY + " REAL, "
					+ StockInfo.BEST_VOL_BUY + " REAL, "
					+ StockInfo.BEST_PRICE_SELL + " REAL, "
					+ StockInfo.BEST_VOL_SELL + " REAL, "
					+ StockInfo.TRADE_DATE + " text);";
			DB.execSQL(DATABASE_CREATE_TABLE);
			// Create table Configuration
			DATABASE_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS "
					+ Config.TABLE_NAME
					+ " (_id integer primary key autoincrement, "
					+ Config.AUTO_CLEAR_TIME + " integer, " + Config.AUTO_SAVE
					+ " integer, " + Config.INTERVAL + " integer, "
					+ Config.IS_REALTIME + " integer);";
			DB.execSQL(DATABASE_CREATE_TABLE);
			// Create table market info
			DATABASE_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS "
					+ MarketInfo.TABLE_NAME
					+ " (_id integer primary key autoincrement, "
					+ MarketInfo.MARKET_ID + " integer, "
					+ MarketInfo.INDEX_CHANGED + " REAL, "
					//+ MarketInfo.COUNT_FLOOR + " REAL, " 
					+ MarketInfo.SET_INDEX
					+ " REAL, " + MarketInfo.STATUS + " text, "
					+ MarketInfo.TIME + " integer, " + MarketInfo.TOTAL_SHARES
					+ " REAL, " + MarketInfo.TOTAL_VALUES + " REAL, "
					+ MarketInfo.TRADE_DATE + " text); ";
			DB.execSQL(DATABASE_CREATE_TABLE);
		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
	}

	@Override
	public synchronized void close() {
		try {
			if (DBSelect != null && DBSelect.isOpen())
				DBSelect.close();
		} catch (Exception ex) {
			Log.d(TAG, ex.toString());
		}
		super.close();
	}

}
